package com.xframe.xdal.core.manage;

import cn.hutool.core.util.ObjectUtil;
import com.xframe.xdal.core.XDataSource;
import com.xframe.xdal.core.constant.DalConstants;
import com.xframe.xdal.core.constant.DataSourceTypeEnums;
import com.xframe.xdal.core.constant.ErrorCode;
import com.xframe.xdal.core.convert.DBConvertInfo;
import com.xframe.xdal.core.convert.IDBTypeStrategy;
import com.xframe.xdal.core.convert.MysqlDBHelper;
import com.xframe.xdal.core.exception.DalFrameException;
import com.xframe.xdal.core.model.mapping.*;
import com.xframe.xdal.core.util.CollectionUtil;
import com.xframe.xdal.core.util.StringUtil;

import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class MysqlDBManage extends BaseDbManage {

    private final String queryTables = "SELECT table_name as tableID, table_name as tableName,TABLE_COMMENT as tableDesc,case when table_type = 'VIEW' then 'view' else 'table' end as tableType FROM information_schema.tables WHERE {} table_schema = '{}'";
    private final String queryTableColumns = "SELECT COLUMN_NAME as columnName,DATA_TYPE as dbDataType,NUMERIC_PRECISION as dataPrecision,CHARACTER_MAXIMUM_LENGTH as dataLength,NUMERIC_SCALE AS dataScale, IS_NULLABLE as nullable,CASE WHEN COLUMN_KEY ='PRI' THEN 1 ELSE 0 END as isPk,\n" +
            "ORDINAL_POSITION as orderNo,TABLE_NAME as tableName,COLUMN_COMMENT as columnDesc,EXTRA as isAutofill,IS_NULLABLE as nullable FROM information_schema.COLUMNS where {} table_schema = '{}';";

    private final String dropTableStructTemplate = "DROP TABLE IF EXISTS `{}`;\n";
    private final String tableStructTemplate = "CREATE TABLE `{}` ({}); {}";
    private final String tableColumnTemplate = "`{}` {} {} COMMENT '{}'{}";
    private final String primaryKeyTemplate = "PRIMARY KEY (`{}`)";
    private final String UniqueIndexTemplate = "CREATE UNIQUE INDEX AK_{}_{} ON {} ({});";
    private final String indexTemplate = "CREATE INDEX IX_{}_{} ON {} ({});";

    IDBTypeStrategy dbTypeHelper;
    INameConvert nameConvert;

    public MysqlDBManage(XDataSource dataSource) {
        super(dataSource);
        nameConvert = NameConvertFactory.create(DataSourceTypeEnums.MySql);
        dbTypeHelper = new MysqlDBHelper();
    }

    @Override
    public List<DBTableInfo> getTableInfoList() {
        try {
            List<DBTableInfo> tableInfos = dataSource.executeQuery(StringUtil.format(this.queryTables, "", dataSource.getDatabaseName()), null, ResultHandler.create(DBTableInfo.class));
            if (CollectionUtil.isEmpty(tableInfos)) {
                return null;
            }

            List<DBColumnInfo> tableColumnInfos = this.getDbColumnInfoList(null);

            List<DBColumnInfo> tmpTableColumnInfo;
            for (DBTableInfo tableInfo : tableInfos) {
                tmpTableColumnInfo = tableColumnInfos.stream().filter(item -> item.getTableName().equals(tableInfo.getTableName())).collect(Collectors.toList());
                if (!CollectionUtil.isEmpty(tmpTableColumnInfo)) {
                    Collections.sort(tmpTableColumnInfo);
                    tableInfo.setDbColumnInfoList(tmpTableColumnInfo);
                }
            }
            return tableInfos;
        }catch (SQLException sqlException){
            throw new DalFrameException(ErrorCode.SQL_ERROR,sqlException.getMessage());
        }
    }

    @Override
    public List<DBColumnInfo> getDbColumnInfoList(String tableName) {

        try {
            String tempTableName = StringUtil.isEmpty(tableName) ? "" : StringUtil.format(" table_name = '{}' and ", tableName);
            List<DBColumnInfo> dbColumnInfos = null;

            dbColumnInfos = dataSource.executeQuery(StringUtil.format(this.queryTableColumns, tempTableName, dataSource.getDatabaseName()),
                    null, ResultHandler.create(DBColumnInfo.class));

            return dbColumnInfos;
        }catch (SQLException sqlException){
            throw new DalFrameException(ErrorCode.SQL_ERROR,sqlException.getMessage());
        }
    }

    @Override
    public Map<String, DBTableInfo> getTableInfoMap(TableKeyMode tableKeyMode) {

        Map<String, DBTableInfo> rtnMapInfo = new HashMap<>();
        List<DBTableInfo> tableInfos = this.getTableInfoList();
        if (CollectionUtil.isEmpty(tableInfos)) {
            return rtnMapInfo;
        }

        tableInfos.forEach(item -> rtnMapInfo.put(item.getTableID(), item));

        return rtnMapInfo;
    }

    @Override
    public DBTableInfo getTableInfo(String tableName, boolean isIncludeColumns) {
        try {
            List<DBTableInfo> tableInfos = null;

            tableInfos = dataSource.executeQuery(StringUtil.format(this.queryTables, " table_name='" + tableName + "' AND ", dataSource.getDatabaseName()), null, ResultHandler.create(DBTableInfo.class));

            if (CollectionUtil.isEmpty(tableInfos)) {
                return null;
            }
            DBTableInfo dbTableInfo = tableInfos.get(0);

            if (isIncludeColumns) {
                List<DBColumnInfo> tableColumnInfos = this.getDbColumnInfoList(tableName);
                dbTableInfo.setDbColumnInfoList(tableColumnInfos);
            }

            return dbTableInfo;
        }catch (SQLException sqlException){
            throw new DalFrameException(ErrorCode.SQL_ERROR,sqlException.getMessage());
        }

    }

    /**
     * 生成表结构
     *
     * @param tableInfo  ITableInfo 对象
     * @param isRecreate 是否重新生成表结构
     * @return 不抛异常即执行成功
     */
    @Override
    public boolean createTable(ITableInfo tableInfo, boolean isRecreate) throws Exception {

        String tableName = tableInfo.getName();
        if (StringUtil.isEmpty(tableName)) {
            return false;
        }


        tableName = nameConvert.toName(tableName);

        List<IColumnMapping> columnMappings = tableInfo.getColumnMappingList();
        if (CollectionUtil.isEmpty(columnMappings)) {
            throw new Exception(StringUtil.format("The table {} not define column info", tableName));
        }
        IColumnMapping pkColumnMapping = tableInfo.findPkFirst();
        if (pkColumnMapping == null) {
            throw new Exception(StringUtil.format("The table {} not define Primary Key", tableName));
        }

        if (!isRecreate) {
            DBTableInfo dbTableInfo = this.getTableInfo(tableName, false);
            if (dbTableInfo != null) {
                throw new Exception(StringUtil.format("The table {} is exists", tableName));
            }
        }
        List<IColumnMapping> uniqueKeys = tableInfo.findUniqueKeyList();

        return this.executeCreateTableSQL(tableName, columnMappings, uniqueKeys, pkColumnMapping,isRecreate,tableInfo.getLabel());
    }





    private StringBuilder getCreateColumnsSql(List<IColumnMapping> columnMappings) {
        StringBuilder columnSql = new StringBuilder();
        Integer columnSize = columnMappings.size();
        for (int i = 0; i < columnSize; i++) {
            DBConvertInfo dbConvertInfo = new DBConvertInfo();
            dbConvertInfo.setJavaType(columnMappings.get(i).getDataType());
            dbConvertInfo.setDataLength(columnMappings.get(i).getLength());
            dbConvertInfo.setScale(columnMappings.get(i).getScale());

            columnSql.append(StringUtil.format(this.tableColumnTemplate,
                    nameConvert.toName(columnMappings.get(i).getName()),
                    dbTypeHelper.getDbType(dbConvertInfo),
                    columnMappings.get(i).getNullFlag() ? "" : " not null ",
                    columnMappings.get(i).getDesc(),
                    DalConstants.SQL_SEPARATION
            ));
        }
        return columnSql;

    }


    /**
     * 删除物理表
     * @param tableInfo
     * @return
     * @throws Exception
     */
    @Override
    public boolean deleteTable(ITableInfo tableInfo) throws Exception {
        if (tableInfo == null) {
            return false;
        }
        String tableName = nameConvert.toName(tableInfo.getName());
        if (StringUtil.isEmpty(tableName)) {
            return false;
        }
        StringBuilder dropTable = new StringBuilder();
        dropTable.append("DROP TABLE ").append(tableName);
        dataSource.execute(dropTable.toString());
        return true;
    }









    private boolean executeCreateTableSQL( String tableName, List<IColumnMapping> columnMappings, List<IColumnMapping> uniqueKeys, IColumnMapping pkColumnMapping,boolean isRecreate,String tableLabel) throws SQLException {

        StringBuilder realSql = this.getCreateColumnsSql(columnMappings);
        StringBuilder uniqueIndex = new StringBuilder();

        realSql.append(StringUtil.format(primaryKeyTemplate, nameConvert.toName(pkColumnMapping.getName())));
        if (!CollectionUtil.isEmpty(uniqueKeys)) {
            String uniqueColumns = "";
            for (IColumnMapping uniqueKey : uniqueKeys) {
                uniqueColumns += nameConvert.toName(uniqueKey.getName()) + DalConstants.SQL_SEPARATION;
            }

            uniqueIndex.append(StringUtil.format(UniqueIndexTemplate,
                    tableName,
                    "1",//后期如果有多个主键序列时，这里依次进行递增
                    tableName,
                    StringUtil.strip(uniqueColumns, DalConstants.SQL_SEPARATION)));
        }

        String sql = "";
        if (isRecreate) {
            sql = StringUtil.format(dropTableStructTemplate + tableStructTemplate, tableName, tableName, realSql, uniqueIndex);
        } else {
            sql = StringUtil.format(tableStructTemplate, tableName, realSql, uniqueIndex);
        }
        if(!StringUtil.isEmpty(tableLabel)){
            sql +=StringUtil.format("ALTER TABLE {} COMMENT '{}';",tableName,tableLabel);
        }

        return dataSource.execute(sql);

    }

    /**
     * 生成历史表结构
     * 生成历史表规则
     * 1.历史表名添加HIS_前缀
     * 2.历史表将原始表主键添加前缀HIS_，并新建新的主键
     * @param tableInfo  ITableInfo 需要生成历史表的对象
     * @return
     */
    @Override
    public boolean createHisTable(ITableInfo tableInfo) throws Exception {
        String tablePrefix="his_";
        if (ObjectUtil.isEmpty(tableInfo)) {
            return false;
        }

        String hisTableName = tablePrefix + nameConvert.toName(tableInfo.getName());
        //判断当前原表名对应的历史表名的表是否存在
        DBTableInfo dbTableInfo = this.getTableInfo(hisTableName, false);
        String tableNameStr = hisTableName + "_{}";
        if (dbTableInfo != null) {
            int i = 1;
            while (true) {
                String tableNameNew = StringUtil.format(tableNameStr, i);
                DBTableInfo dbTableInfoLoop = this.getTableInfo(tableNameNew, false);
                if (dbTableInfoLoop == null) {
                    hisTableName = tableNameNew;
                    break;
                }
                i ++;
            }
        }

        ITableInfo hisTableInfo = this.packHisTableInfo(tableInfo,hisTableName);
        return this.createTable(hisTableInfo, false);

    }


    // SELECT table_name as tableName FROM information_schema.tables WHERE table_schema = '%s'
    // SELECT table_name as tableID, table_name as tableName,TABLE_COMMENT as tableDesc,case when table_type = 'VIEW' then 'view' else 'table' end as tableType FROM information_schema.tables WHERE table_schema = '%s'
}
